package com.zhj.util.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * description: 基于阿里EasyExcel封装的工具类
 */
@SuppressWarnings("deprecation")
public class ExcelUtil {
    /**
     * 私有化构造方法
     */
    private ExcelUtil(){}

    /**
     * 读取 Excel(多个 sheet)
     * 将多sheet合并成一个list数据集，通过自定义ExcelReader继承AnalysisEventListener
     * 重写invoke doAfterAllAnalysed方法
     * getExtendsBeanList 主要是做Bean的属性拷贝 ，可以通过ExcelReader中添加的数据集直接获取
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T>  rowModel) throws ExcelException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return new ArrayList<>();
        }
        for (Sheet sheet : reader.getSheets()) {
            sheet.setClazz(rowModel);
            reader.read(sheet);
        }
        return getExtendsBeanList(excelListener.getDataList(),rowModel);
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T>  rowModel, int sheetNo)  throws ExcelException{
        return readExcel(excel, rowModel, sheetNo, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static <T extends BaseRowModel> List<T> readExcel(MultipartFile excel, Class<T>  rowModel, int sheetNo,
                                                             int headLineNum) throws ExcelException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return new ArrayList<>();
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel));
        return getExtendsBeanList(excelListener.getDataList(),rowModel);
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     * 自定义WriterHandler 可以定制行列数据进行灵活化操作
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     */
    public static <T extends BaseRowModel> void writeExcel(HttpServletResponse response, List list,
                                                           String fileName, String sheetName, ExcelTypeEnum excelTypeEnum,
                                                           Class classType) throws ExcelException, UnsupportedEncodingException {
//        fileName = URLEncoder.encode(fileName,"utf-8");
        if(sheetName == null || "".equals(sheetName)){
            sheetName = "sheet1";
        }
        if(excelTypeEnum == ExcelTypeEnum.XLSX) {
            ExcelWriter writer = EasyExcelFactory.getWriterWithTempAndHandler(null,
                    getOutputStream(fileName, response, excelTypeEnum), excelTypeEnum, true, new WriterHandler07<>(classType));
            Sheet sheet = new Sheet(1, 0, classType);
            sheet.setSheetName(sheetName);
            try {
                writer.write(list, sheet);
            } finally {
                writer.finish();
            }
            //其实也可以专门调03版的样式，或者直接套用
        } else if(excelTypeEnum == ExcelTypeEnum.XLS ){
            ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response,excelTypeEnum), excelTypeEnum);
            Sheet sheet = new Sheet(1, 0, classType);
            sheet.setSheetName(sheetName);
            try {
                writer.write(list, sheet);
            } finally {
                writer.finish();
                writer.close();
            }
        }

    }
    /**
     * 导出 Excel ：一个 sheet，动态表头
     * @param response
     * @param list   数据集
     * @param fileName  文件
     * @param sheetName
     * @param excelTypeEnum  导出Excel格式
     * @param head			 表头JSON
     * @param codeStyle		 样式JSON
     * @throws ExcelException
     * @throws UnsupportedEncodingException
     */
	@SuppressWarnings("unchecked")
	public static void writeExcelDynamic(HttpServletResponse response, List<LinkedHashMap<String, Object>> list,
                                         String fileName, String sheetName, ExcelTypeEnum excelTypeEnum,
                                         String dynamicHeadVoJson) throws ExcelException, UnsupportedEncodingException {
        if(sheetName == null || "".equals(sheetName)){
            sheetName = "sheet1";
        }
//        //获取表头
//        List<List<String>> header = ExcelStyleHandler.getHeader(head);
//        //获取宽度
//        Map<Integer, Integer>columnWidthMap = ExcelStyleHandler.getColumnWidth(codeStyle);
//        //获取处理过的数据
//        List<List<Object>> dataInfo=ExcelStyleHandler.getDataInfo(list);
        //获取表头列宽 处理过的数据
        Map<String, Object> param=ExcelStyleHandler.getHeadAndWidthAndProp(dynamicHeadVoJson);
        List<List<String>> header =( List<List<String>>)param.get("result_head");
        Map<Integer, Integer>columnWidthMap = ExcelStyleHandler.getColumnWidthV2((List<Map<Integer,Integer>>)param.get("result_width"));
        List<List<Object>> dataInfo=ExcelStyleHandler.getDataInfoV2(list,(List<String>)param.get("result_prop"));
        //获取表头样式
        WriteCellStyle headWriteCellStyle = ExcelStyleHandler.setHeadStyle();
        //获取内容样式
        WriteCellStyle contentWriteCellStyle = ExcelStyleHandler.setContentStyle();

        //判断下载格式
        if(excelTypeEnum == ExcelTypeEnum.XLSX) {

        	//设置EXCEL
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            //
            ExcelWriter  excelWriter=  EasyExcel.write(getOutputStream(fileName, response, excelTypeEnum)).head(header).registerWriteHandler(horizontalCellStyleStrategy).build();
            try {
            //设置sheet
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            	writeSheet.setColumnWidthMap(columnWidthMap);
                excelWriter.write(dataInfo, writeSheet);
            }catch (Exception e) {
				e.printStackTrace();
			}finally {
				excelWriter.finish();
            }
            //其实也可以专门调03版的样式，或者直接套用
        } else if(excelTypeEnum == ExcelTypeEnum.XLS ){
            	HorizontalCellStyleStrategy horizontalCellStyleStrategy =  new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
                ExcelWriter  excelWriter=  EasyExcel.write(getOutputStream(fileName, response, excelTypeEnum)).head(header).registerWriteHandler(horizontalCellStyleStrategy).build();
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
                writeSheet.setColumnWidthMap(columnWidthMap);
                excelWriter.write(dataInfo, writeSheet);
                 try {
                    excelWriter.write(dataInfo, writeSheet);
                }catch (Exception e) {
    				e.printStackTrace();
    			}finally {
    				excelWriter.finish();
                }
            }

    }
    /**
     * 导出 Excel ：多个 sheet，带表头
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list,
                                                          String fileName, String sheetName, BaseRowModel object, ExcelTypeEnum excelTypeEnum) throws ExcelException, UnsupportedEncodingException {
//        fileName = URLEncoder.encode(fileName,"utf-8");
        ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response,excelTypeEnum), excelTypeEnum);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        return writer;
    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response, ExcelTypeEnum excelTypeEnum) throws ExcelException, UnsupportedEncodingException {
        fileName = URLEncoder.encode(fileName,"utf-8");
        //创建本地文件
        String filePath = fileName + excelTypeEnum.getValue();
        try {
            fileName = new String(filePath.getBytes(), "ISO-8859-1");
            response.setContentType("application/force-download");
            response.addHeader("Content-Disposition", "filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            throw new ExcelException("创建文件失败！");
        }
    }

    /**
     * 返回 ExcelReader
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel,
                                         ExcelListener excelListener) throws ExcelException{
        String fileName = excel.getOriginalFilename();
        if (fileName == null ) {
            throw new ExcelException("文件格式错误！");
        }
        if (!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue()) && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())) {
            throw new ExcelException("文件格式错误！");
        }
        InputStream inputStream;
        try {
            inputStream = excel.getInputStream();
            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            //do something
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 利用BeanCopy转换list
     */
    public static <T extends BaseRowModel> List<T> getExtendsBeanList(List<?> list, Class<T> typeClazz){
        return MyBeanCopy.convert(list,typeClazz);
    }

}
